import os
import subprocess
import sys
import time
import traceback
from concurrent.futures import ThreadPoolExecutor

import pyautogui
import pymysql
import pyperclip
import pywinauto
import win32con
import win32gui
from PIL import ImageGrab
from win32gui import FindWindow


os.system('chcp 65001')

def sql_add(sql_text):
    conn = pymysql.connect(host="127.0.0.1", user="root", database="individual_tax", password="753951", charset="utf8")
    cursor = conn.cursor()
    cursor.execute(sql_text)
    conn.commit()
    cursor.close()
    conn.close()


def sql_query(sql_text):
    conn = pymysql.connect(host="127.0.0.1", user="root", database="individual_tax", password="753951", charset="utf8")

    cursor = conn.cursor()
    sql_len = cursor.execute(sql_text)
    sql_tuplet = []
    for i in range(sql_len):
        sql_tuplet.append(list(cursor.fetchone()))
    cursor.close()
    conn.close()
    return sql_len, sql_tuplet


def sql_update(sql_text):
    conn = pymysql.connect(host="127.0.0.1", user="root", database="individual_tax", password="753951", charset="utf8")
    cursor = conn.cursor()
    cursor.execute(sql_text)
    conn.commit()
    cursor.close()
    conn.close()


def cordow(jpg_name2, jpg_name4, jpg_name5,jpg_name6, num):
    i = 0
    j = 0
    k = 450
    time.sleep(10)

    pyautogui.press('esc')

    # 下载更新
    while j < 5:
        j = j + 1
        print('k值', k)
        time.sleep(10)
        xe = pyautogui.locateOnScreen(jpg_name5, confidence=0.7)
        goto_pos = pyautogui.center(xe)
        pyautogui.click(goto_pos, interval=1, duration=2)
        time.sleep(10)
        # 确认全员
        xt = pyautogui.locateOnScreen(jpg_name6, confidence=0.7)
        if xt:
            goto_pos = pyautogui.center(xt)
            pyautogui.click(goto_pos, interval=1, duration=2)
            break

    while i < num:
        time.sleep(4)
        xx = pyautogui.locateOnScreen(jpg_name2, confidence=0.8)
        if xx:
            print('i值', i)
            k = k - 1
            if k == 0:
                raise Exception('等待超时')
            else:
                continue

        ss = pyautogui.locateOnScreen(jpg_name4, confidence=0.8)
        if ss:
            goto_pos = pyautogui.center(ss)
            pyautogui.click(goto_pos, interval=1, duration=2)
            time.sleep(10)
            pyautogui.hotkey('alt', 'f4')
            break

        i = i + 1
        if i == num - 1:
            f = open('log.txt', 'a+')
            f.write(traceback.format_exc())
            f.close()
            error = '等待超时' + jpg_name2
            error_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())

            sql_text = 'insert into error_table(error,error_time) ' \
                       'values(\'{}\',\'{}\')'.format(error, error_time)
            sql_add(sql_text)
            raise Exception('等待超时')


def corjpg(jpg_name, num):
    for i in range(num):
        print(jpg_name)
        time.sleep(1)
        xy = pyautogui.locateOnScreen(jpg_name, confidence=0.8)

        if xy:
            goto_pos = pyautogui.center(xy)
            pyautogui.click(goto_pos, interval=1, duration=2)
            break
        if i == num-1:
            error = '等待超时' + jpg_name
            error_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())

            sql_text = 'insert into error_table(error,error_time) ' \
                       'values(\'{}\',\'{}\')'.format(error,error_time)
            sql_add(sql_text)
            raise Exception('等待超时')


def corjpg_on(jpg_name, num):
    for i in range(num):
        time.sleep(1)
        xy = pyautogui.locateOnScreen(jpg_name, confidence=0.8)
        if xy:
            j = 0
            while j < 1000:
                print(jpg_name)
                time.sleep(1)
                xx = pyautogui.locateOnScreen(jpg_name, confidence=0.8)
                if xx:
                    if j >= 998:
                        error = '等待超时' + jpg_name
                        error_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())

                        sql_text = 'insert into error_table(error,error_time) ' \
                                   'values(\'{}\',\'{}\')'.format(error, error_time)
                        sql_add(sql_text)
                        raise Exception('等待超时')
                    j = j + 1
                else:
                    break
            break
        if i == num-1:
            error = '等待超时' + jpg_name
            error_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())

            sql_text = 'insert into error_table(error,error_time) ' \
                       'values(\'{}\',\'{}\')'.format(error, error_time)
            sql_add(sql_text)
            raise Exception('等待超时')


def del_file(n):
    path_file = r'C:\file_excel\\' + n

    # 判断文件夹书否存在
    print(n)
    if not os.path.exists('C:\\file_excel\\' + n):
        os.mkdir('C:\\file_excel\\' + n)
        print('执行')
    # 获取文件夹下所有文件名称（列表）
    ls = os.listdir(path_file)
    for i in ls:
        # 单个图片路径组装
        f_path = os.path.join(path_file, i)
        # 删除文件
        os.remove(f_path)


def run_task(t, p, n):

    j_path = r'C:\imgs'
    # 判断之前个税程序是否启动
    handler = FindWindow(None, '自然人电子税务局（扣缴端）')

    if handler:
        # 删除程序
        # win32gui.SetForegroundWindow(handler)
        # win32gui.ShowWindow(handler, win32con.SW_MAXIMIZE)
        os.system('wmic process where name=\'EPPortalITS.exe\' delete')
        os.system('wmic process where name=\'EPDataMgrTools.exe\' delete')

    time.sleep(10)
    handler = FindWindow(None, 'frm_LoginViewer')
    if not handler:
        # 启动程序
        os.environ.update({'__COMPAT_LAYER': 'RUnAsInvoker'})
        path = r'C:\ITSKHD\EPPortal_DS3.0\EPEvenue_SH.exe'
        subprocess.Popen(path)

        time.sleep(10)
        # 点击密码登录
        image_name = j_path + r'\1.jpg'
        corjpg(image_name, 60)

        # 选择公司
        time.sleep(2)
        pyautogui.press('tab')
        time.sleep(2)
        pyautogui.press('tab')
        time.sleep(2)
        pyautogui.press('end')
        time.sleep(2)
        # 删除公司信息
        for i in range(20):
            pyautogui.press('backspace')
        # 输入公司名称
        time.sleep(2)
        pyperclip.copy(n)
        pyautogui.hotkey('ctrl', 'v')
        time.sleep(2)
        pyautogui.press('tab')
        time.sleep(2)
        pyautogui.press('down')
        time.sleep(2)
        pyautogui.press('enter')

        # 点击密码输入
        image_name = j_path + r'\2.jpg'
        corjpg(image_name, 60)

        # 输入密码
        pyautogui.typewrite(p)

        # 确认密码
        pyautogui.press('enter')
        pyautogui.press('enter')

        # 点击专项信息采集
        image_name =j_path + r'\3.jpg'
        corjpg(image_name, 60)

        # 下载
        image_name2, image_name4, image_name5, image_name6\
            = j_path + r'\doing.jpg', j_path + r'\14.jpg', j_path + r'\15.jpg', j_path + r'\16.jpg'
        cordow(image_name2, image_name4, image_name5, image_name6, 60)

        # 点击导出
        image_name = j_path + r'\4.jpg'
        corjpg(image_name, 60)
        # 点击全部导出
        image_name = j_path + r'\5.jpg'

        corjpg(image_name, 60)

        # 点击列表样式
        image_name = j_path + r'\6.jpg'
        corjpg(image_name, 60)

        # 保存
        image_name = j_path + r'\7.jpg'
        corjpg(image_name, 60)

        # 判断是否打开保存文件窗口
        image_name = j_path + r'\10.jpg'
        corjpg(image_name, 60)

        # 删除文件夹内容
        del_file(n)

        # 文件保存路径
        file_path = 'C:\\file_excel\\' + n + '\\'+ t + '专项附加扣除信息.xlsx'
        # pyautogui.write(file_path)

        pyperclip.copy(file_path)
        # 复制内容
        pyautogui.hotkey('ctrl', 'V')
        # 确认保存
        pyautogui.press('enter')

        # 如文件重复按tab加空格
        pyautogui.press('tab')
        pyautogui.press('enter')

        # 判断是否导出成功
        image_name = j_path + r'\11.jpg'
        corjpg(image_name, 60)
        # 导出成功确认
        pyautogui.press('enter')

        # 关闭程序
        time.sleep(3)
        handler = FindWindow(None, '自然人电子税务局（扣缴端）')
        win32gui.SetForegroundWindow(handler)
        # win32gui.ShowWindow(handler, win32con.SW_MAXIMIZE)
        pyautogui.hotkey('alt', 'f4')
        time.sleep(3)
        pyautogui.press('enter')

        # 确认是否在备份
        image_name = j_path + r'\8.jpg'
        corjpg_on(image_name, 60)

        # 确认备份
        image_name = j_path + r'\9.jpg'
        corjpg(image_name, 60)

        pyautogui.press('enter')

        # 导出成功数据库信息确认
        sql_text = 'update messages set wait_task=\'n\',task_set=\'0\' where comany_name=\'{}\';'.format(n)
        sql_update(sql_text)


if __name__ == '__main__':
    executor = ThreadPoolExecutor(1)

    # 选择初始化任务类型
    sql_text = 'select default_value from set_table;'
    sql_len, sql_tulpt = sql_query(sql_text)
    default_v = sql_tulpt[0][0]
    # 初始化任务
    sql_text = 'update messages set task_set=\'' + default_v + '\' where task_set=\'0\';'
    sql_update(sql_text)
    sql_text = 'update messages set wait_task=\'n\';'
    sql_update(sql_text)

    # 运行窗口最小化
    hwnd = win32gui.FindWindow('ConsoleWindowClass', None)
    win32gui.ShowWindow(hwnd, win32con.SW_SHOWMINIMIZED)
    
    while True:
        try:
            time.sleep(1)

            print('start')

            # 定时任务
            fixed_time = time.strftime("%H:%M", time.localtime())
            # file_set_time = time.strftime("%Y-%H-%M_", time.localtime())
            file_set_time = time.strftime("%Y-%m-%d_", time.localtime())
            int_time = int(time.strftime("%H", time.localtime()))

            sql_text = 'select * from set_table;'
            sql_len, sql_list = sql_query(sql_text)
            moring_start_time = int(sql_list[0][0])
            moring_end_time = int(sql_list[0][1])
            night_start_time = int(sql_list[0][2])
            night_end_time = int(sql_list[0][3])

            # 判断运行时间
            if moring_start_time <= int_time <= moring_end_time or night_start_time <= int_time <= night_end_time:
                # 判断是否存在运行程序
                sql_text = 'select wait_task from messages where wait_task=\'y\';'
                sql_len, sql_list = sql_query(sql_text)
                print(sql_list)
                if not sql_len:
                    # 数据库信息
                    sql_text = 'select comany_name,password,task_set from messages where task_set !=\'0\';'
                    sql_len, sql_list = sql_query(sql_text)
                    passwords = '0'
                    name = 'name'
                    if sql_len:
                        # 优先处理手动添加任务
                        for i in range(sql_len):
                            if sql_list[i][2] == 's':
                                # 密码

                                passwords = sql_list[i][1]

                                # 公司名
                                name = sql_list[i][0]
                                break
                        if passwords == '0':
                            # 次处理自动添加任务
                            for i in range(sql_len):
                                if sql_list[i][2] == 'z':
                                    # 密码
                                    passwords = sql_list[i][1]
                                    # 公司名
                                    name = sql_list[i][0]
                                    break
                        time.sleep(1)
                        if passwords != '0':
                            sql_text = 'update messages set wait_task = \'y\' where comany_name=\'{}\';'.format(name)
                            sql_update(sql_text)
                            maker = executor.submit(run_task, file_set_time, passwords, name)
                            # 获取结果，主要获取报错结果
                            res = maker.result()

        except Exception as e:
            # 重新执行任务
            print('重启')
            print(traceback.format_exc())
            time.sleep(30)

            try:
                img_time = time.strftime("%Y-%m-%d%H%M%S", time.localtime())
                im = ImageGrab.grab()
                im.save('C:\\errorimg\\{}.png'.format(img_time))
            except Exception as e:
                error = '系统故障，无法正常使用图形桌面'
            sql_update(sql_text)

            os.system('wmic process where name=\'EPPortalITS.exe\' delete')
            os.system('wmic process where name=\'EPDataMgrTools.exe\' delete')

            sql_text = 'update messages set wait_task=\'n\';'
            sql_update(sql_text)

            f = open('log.txt', 'a+')
            f.write(traceback.format_exc())
            f.close()

            error = '任务重启'
            error_time = time.strftime("%Y-%m-%d%H:%M:%S", time.localtime())

            sql_text = 'insert into error_table(error,error_time) ' \
                       'values(\'{}\',\'{}\')'.format(error, error_time)
            sql_add(sql_text)





